import os
import re
import openpyxl
from openpyxl import load_workbook
import sys
import io
import tkinter as tk
from tkinter import messagebox, ttk

# 设置输出编码为UTF-8
if sys.platform == 'win32':
    sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

# 自定义翻译映射表 - 从bom_translator.py复制
CUSTOM_TRANSLATIONS = {
    '版本记录': 'Revision_History',
    '产品名称：': 'Product:',
    '产品型号：': 'Short Name:',
    '日期：': 'Date:',
    '初版': 'First Version',
    'BOM版本': 'BOM Version:',
    '版本变更记录': 'Revision History',
    '版本': 'Version',
    '日期': 'Date',
    '变更内容': 'Changes',
    '变更前': 'Change before',
    '变更后': 'Change after',
    '产品型号': 'Short Name',
    '产品名称': 'Product Name',
    '版本号': 'Version',
    '释放/更新日期': 'Release/Update Date',
    '序号': 'Item #',
    '位           号': 'Designator',
    '用量': 'Quantity',
    '元 件 名 称': 'Description',
    '参     数': 'Value',
    '封       装': 'Footprint',
    '物料编码': 'Part Number',
    '备注': 'Comment',
    '贴片电容': 'MLCC',
    '电池触点弹片，镀金，正极': 'Battery contact,gold plated,plus',
    '电池触点弹片，镀金，负极': 'Battery contact,gold plated,minus',
    '肖特基二极管': 'SBD',
    '发光二极管RGB三色': 'LED RGB',
    '发光二极管 White白光': 'LED White',
    'FPC连接器，下接，间距0.5mm，max高1.0mm': 'FPC connector,bottom contact,pitch 0.5mm，max height 1.0mm',
    '层叠电感': 'SMD Inductor',
    '功率电感': 'Power Inductor',
    '贴片无源晶体': 'SMD Crystal',
    '贴片电阻': 'SMD Resistor',
    '寸': '"'
}

# 需要删除的关键词
TO_REMOVE = ['(三星)', '(村田)', '(国巨)', '(顺络)', '(太诱)', '(惠伦)', '(长晶)', '(风华)', '(奇力新)']

class AlternativePartAdder:
    def __init__(self, root):
        self.root = root
        self.root.title("BOM替代料添加工具")
        self.root.geometry("400x300")
        self.root.resizable(False, False)
        
        # 设置中文字体
        self.font = ('SimHei', 10)
        
        # 创建存储输入序号的列表
        self.index_entries = []
        
        # 创建GUI界面
        self.create_widgets()
        
        # 存储选中的替代料数据
        self.selected_alternatives = []
        
        # 窗口关闭事件处理
        self.root.protocol("WM_DELETE_WINDOW", self.on_closing)
    
    def create_widgets(self):
        # 创建主框架
        main_frame = ttk.Frame(self.root, padding="20")
        main_frame.pack(fill=tk.BOTH, expand=True)
        
        # 创建标题标签
        title_label = ttk.Label(main_frame, text="新增物料序号（Index）：请输入序号数字")
        title_label.pack(anchor=tk.W, pady=(0, 15))
        
        # 创建输入框框架
        entries_frame = ttk.Frame(main_frame)
        entries_frame.pack(fill=tk.X, pady=5)
        
        # 创建5个输入框
        for i in range(5):
            frame = ttk.Frame(entries_frame)
            frame.pack(fill=tk.X, pady=3)
            
            # 序号标签
            num_label = ttk.Label(frame, text=f"{i+1}", width=5)
            num_label.pack(side=tk.LEFT, padx=(0, 10))
            
            # 输入框
            entry = ttk.Entry(frame)
            entry.pack(side=tk.LEFT, fill=tk.X, expand=True)
            
            # 添加验证器，只允许数字输入
            vcmd = (self.root.register(self.validate_number), '%P')
            entry.configure(validate="key", validatecommand=vcmd)
            
            self.index_entries.append(entry)
        
        # 创建底部框架
        bottom_frame = ttk.Frame(main_frame)
        bottom_frame.pack(fill=tk.X, pady=(20, 0))
        
        # 确定按钮
        self.confirm_button = ttk.Button(bottom_frame, text="确定", command=self.on_confirm, width=20)
        self.confirm_button.pack(side=tk.RIGHT)
    
    def validate_number(self, value):
        # 允许空字符串或纯数字
        if value == "":
            return True
        return value.isdigit()
    
    def on_confirm(self):
        try:
            # 收集输入的序号
            indices = []
            for entry in self.index_entries:
                text = entry.get().strip()
                if text:
                    try:
                        index = int(text)
                        indices.append(index)
                        print(f"添加序号: {index}")
                    except ValueError:
                        messagebox.showerror("输入错误", f"请输入有效的数字: {text}")
                        self.root.destroy()
                        return
            
            if not indices:
                messagebox.showwarning("提示", "请至少输入一个序号")
                self.root.destroy()
                return
            
            # 读取替代料数据
            self.read_alternative_data(indices)
            
            # 搜索并处理Excel文件
            self.process_excel_files()
            
            messagebox.showinfo("成功", f"已成功处理 {len(self.selected_alternatives)} 个替代料")
            
            # 处理完成后立即关闭窗口
            print("处理完成，关闭窗口")
            self.root.destroy()
            
        except Exception as e:
            # 直接显示异常消息，不添加额外前缀
            error_msg = str(e)
            print(f"错误: {error_msg}")
            messagebox.showerror("错误", error_msg)
            # 发生错误也关闭窗口
            self.root.destroy()
    
    def read_alternative_data(self, indices):
        print(f"读取替代料数据，序号: {indices}")
        
        # 固定文件名
        tracker_file = "BOEDT Alternative Tracker.xlsx"
        if not os.path.exists(tracker_file):
            raise FileNotFoundError(f"替代料跟踪文件不存在: {tracker_file}")
        
        # 打印文件信息
        print(f"文件大小: {os.path.getsize(tracker_file)} 字节")
        
        workbook = load_workbook(tracker_file, data_only=True)
        print(f"工作簿包含工作表: {workbook.sheetnames}")
        
        # 严格匹配工作表名
        if "Tracker" not in workbook.sheetnames:
            workbook.close()
            raise ValueError("未找到Tracker工作表")
        else:
            sheet = workbook["Tracker"]
            print(f"选择Tracker工作表")
        
        print(f"工作表尺寸: {sheet.max_row} 行 x {sheet.max_column} 列")
        
        # 严格查找Index列 - 在第2行查找
        index_col = None
        header_row = 2
        
        # 检查第2行作为表头
        for col in range(1, sheet.max_column + 1):
            cell_value = sheet.cell(row=2, column=col).value
            if cell_value:
                cell_str = str(cell_value).strip().lower()
                print(f"检查行2列{col}: '{cell_str}'")
                # 严格匹配Index列
                if cell_str == "index" or cell_str == "序号" or cell_str == "#id":
                    index_col = col
                    print(f"找到Index列: 第{col}列, 原始值: '{cell_value}'")
                    break
        
        if index_col is None:
            workbook.close()
            raise ValueError("未找到Index列，请确保第一行包含'Index'或'序号'列")
        
        # 打印表头信息
        print(f"表头行: {header_row}, Index列: {index_col}")
        print("表头内容:")
        for col in range(1, min(sheet.max_column + 1, 10)):  # 打印前10列的表头
            header_value = sheet.cell(row=header_row, column=col).value
            print(f"  列{col}: '{header_value}'")
        
        # 查找并收集数据 - 从表头行+1开始搜索
        self.selected_alternatives = []
        start_row = header_row + 1
        
        print(f"从第{start_row}行开始搜索数据")
        
        # 存储已检查的Index值用于调试
        checked_indices = []
        
        # 严格匹配，不做任何转换或容错
        for row in range(start_row, sheet.max_row + 1):
            cell_value = sheet.cell(row=row, column=index_col).value
            
            # 记录已检查的值
            if cell_value is not None:
                checked_indices.append((row, cell_value))
            
            # 严格匹配，只接受整数类型
            if isinstance(cell_value, int):
                print(f"检查行{row}的Index值: {cell_value}, 目标值: {indices}")
                
                # 直接匹配用户输入的序号
                if cell_value in indices:
                    print(f"找到匹配行: 第{row}行, Index值: {cell_value}")
                    data = self._get_alternative_data(sheet, row, header_row)
                    if data:
                        # 添加索引信息便于调试
                        data['_matched_index'] = cell_value
                        data['_matched_row'] = row
                        self.selected_alternatives.append(data)
                        print(f"  成功添加替代料数据: {data.get('1st Source MPN')} -> {data.get('Alternative MPN')}")
                        print(f"  描述: {data.get('Description')}")
                        print(f"  供应商: {data.get('Alternative Supplier')}")
        
        # 打印调试信息
        print(f"已检查 {len(checked_indices)} 个Index值")
        print("最近检查的Index值:")
        for row, value in checked_indices[-20:]:  # 显示最近20个
            print(f"  行{row}: '{value}'")
        
        workbook.close()
        
        if not self.selected_alternatives:
            # 严格的错误信息
            error_msg = f"未找到与序号 {set(indices)} 匹配的替代料数据。已处理Tracker文件:BOEDT Alternative Tracker.xlsx已处理行数:{len(checked_indices)}\n"
            error_msg += "请检查:\n"
            error_msg += "1.输入的序号是否正确\n"
            error_msg += "2.Tracker文件中的序号格式\n"
            error_msg += "3.Index列是否正确识别"
            raise ValueError(error_msg)
        
        print(f"读取完成，共找到 {len(self.selected_alternatives)} 个替代料")
    
    def _get_alternative_data(self, sheet, row, header_row):
        """获取指定行的替代料数据 - 严格匹配"""
        data = {}
        
        print(f"收集行 {row} 的数据... (表头行: {header_row})")
        
        # 搜索所有列
        for col in range(1, sheet.max_column + 1):
            # 从指定的表头行获取表头
            header = sheet.cell(row=header_row, column=col).value
            if header:
                header_str = str(header).strip().lower()
                cell_value = sheet.cell(row=row, column=col).value
                print(f"  列{col}: '{header_str}' = '{cell_value}'")
                
                # 严格匹配列名
                if header_str == "1st source mpn":
                    data["1st Source MPN"] = cell_value
                elif header_str == "alternative mpn":
                    data["Alternative MPN"] = cell_value
                elif header_str == "description":
                    data["Description"] = cell_value
                elif header_str == "alternative supplier":
                    data["Alternative Supplier"] = cell_value
        
        # 打印收集到的数据
        print(f"  收集的数据: {data}")
        
        # 严格检查必需字段
        required_fields = ["1st Source MPN", "Alternative MPN", "Description", "Alternative Supplier"]
        for field in required_fields:
            if field not in data or data[field] is None:
                print(f"  缺少必需字段: {field}，跳过此行")
                return None
        
        return data
    
    def translate_text(self, text):
        """翻译文本，使用自定义翻译映射表"""
        if not text or not isinstance(text, str):
            return text
            
        # 先尝试完全匹配
        if text in CUSTOM_TRANSLATIONS:
            return CUSTOM_TRANSLATIONS[text]
            
        # 检查是否包含需要删除的关键词
        for item in TO_REMOVE:
            if item in text:
                text = text.replace(item, '')
                
        # 尝试部分匹配和替换
        translated_text = text
        for key, value in CUSTOM_TRANSLATIONS.items():
            if key in translated_text:
                translated_text = translated_text.replace(key, value)
                
        return translated_text
    
    def process_excel_files(self):
        print("开始处理Excel文件")
        
        # 获取当前目录下的所有Excel文件
        current_dir = os.getcwd()
        excel_files = []
        
        for file in os.listdir(current_dir):
            # 排除临时文件和跟踪文件
            if file.startswith('~$'):
                continue
            if file == "BOEDT Alternative Tracker.xlsx":
                continue
            if file.endswith('.xlsx') or file.endswith('.xls'):
                full_path = os.path.join(current_dir, file)
                excel_files.append(full_path)
                print(f"添加文件: {full_path}")
        
        if not excel_files:
            raise ValueError("未找到要处理的BOM文件")
        
        print(f"找到 {len(excel_files)} 个BOM文件")
        
        # 处理每个Excel文件
        for file_path in excel_files:
            try:
                print(f"处理文件: {file_path}")
                self.process_bom_file(file_path)
            except Exception as e:
                print(f"处理文件 {file_path} 错误: {str(e)}")
                # 继续处理下一个文件
    
    def process_bom_file(self, file_path):
        # 加载工作簿
        workbook = load_workbook(file_path)
        
        # 严格检查是否有BOM工作表
        if "BOM" not in workbook.sheetnames:
            workbook.close()
            raise ValueError("未找到BOM工作表")
        
        sheet = workbook["BOM"]
        print(f"选择BOM工作表")
        
        # 严格查找必要的列
        column_mapping = {}
        
        # 检查第1-10行以找到列名
        found_headers = False
        for row in range(1, 11):
            print(f"检查BOM工作表第{row}行")
            for col in range(1, sheet.max_column + 1):
                header = sheet.cell(row=row, column=col).value
                if header:
                    header_str = str(header).strip().lower()
                    print(f"检查BOM列 {col}: '{header_str}'")
                    
                    # 严格匹配英文列名
                    if header_str == "mpn" or header_str == "part number":
                        column_mapping["MPN"] = col
                        print(f"找到MPN列: 第{col}列, 原始值: '{header}'")
                    elif header_str == "description":
                        column_mapping["Description"] = col
                        print(f"找到Description列: 第{col}列, 原始值: '{header}'")
                    elif header_str == "manufacturer" or header_str == "supplier":
                        column_mapping["Manufacturer"] = col
                        print(f"找到Manufacturer列: 第{col}列, 原始值: '{header}'")
            
            # 如果这一行找到了所有必要的列，就停止查找
            if all(key in column_mapping for key in ["MPN", "Description", "Manufacturer"]):
                found_headers = True
                print(f"在第{row}行找到所有必要的列")
                break
        
        # 特殊处理：如果没找到MPN但找到了'Manufacturer Part Number'
        if "MPN" not in column_mapping:
            for col in range(1, sheet.max_column + 1):
                header = sheet.cell(row=row, column=col).value  # 使用最后检查的行
                if header and str(header).strip().lower() == "manufacturer part number":
                    column_mapping["MPN"] = col
                    print(f"找到Manufacturer Part Number列作为MPN: 第{col}列, 原始值: '{header}'")
                    break
        
        # 严格检查必要的列是否存在
        missing_columns = []
        if "MPN" not in column_mapping:
            missing_columns.append("MPN/Part Number/Manufacturer Part Number")
        if "Description" not in column_mapping:
            missing_columns.append("Description")
        if "Manufacturer" not in column_mapping:
            missing_columns.append("Manufacturer/Supplier")
        
        if missing_columns:
            workbook.close()
            raise ValueError(f"BOM工作表缺少必要列: {', '.join(missing_columns)}")
        
        print(f"BOM列映射完成: {column_mapping}")
        
        # 查找并添加替代料
        rows_to_add = []
        
        for row in range(2, sheet.max_row + 1):
            mpn_value = sheet.cell(row=row, column=column_mapping["MPN"]).value
            if mpn_value:
                mpn_str = str(mpn_value)
                # 查找匹配的替代料
                for alt_part in self.selected_alternatives:
                    if alt_part["1st Source MPN"] == mpn_str:
                        print(f"找到匹配MPN: {mpn_str} 在第{row}行")
                        # 准备要添加的行数据
                        new_row_data = {}
                        for col_name, col_index in column_mapping.items():
                            if col_name == "Description":
                                # 翻译描述
                                translated_desc = self.translate_text(alt_part["Description"])
                                new_row_data[col_index] = translated_desc
                            elif col_name == "MPN":
                                new_row_data[col_index] = alt_part["Alternative MPN"]
                            elif col_name == "Manufacturer":
                                new_row_data[col_index] = alt_part["Alternative Supplier"]
                        
                        # 记录要添加的行
                        rows_to_add.append((row + 1, new_row_data))  # +1 是因为要在当前行下方添加
                        break
        
        print(f"需要添加 {len(rows_to_add)} 行")
        
        # 从下往上添加行，避免行号变化问题
        rows_to_add.sort(reverse=True)
        for row_num, row_data in rows_to_add:
            try:
                sheet.insert_rows(row_num)
                for col_index, value in row_data.items():
                    sheet.cell(row=row_num, column=col_index).value = value
                print(f"已添加行: {row_num}")
            except Exception as e:
                print(f"添加行 {row_num} 错误: {str(e)}")
        
        # 保存文件（使用临时文件避免权限问题）
        try:
            import os
            import shutil
            
            # 创建临时文件名
            temp_file_path = file_path + '.temp'
            print(f"保存到临时文件: {temp_file_path}")
            workbook.save(temp_file_path)
            
            # 关闭工作簿以释放文件锁
            workbook.close()
            
            # 尝试重命名临时文件（如果原文件存在，先删除）
            if os.path.exists(file_path):
                try:
                    os.remove(file_path)
                except:
                    # 如果删除失败，尝试直接重命名
                    pass
            
            # 重命名临时文件
            shutil.move(temp_file_path, file_path)
            print(f"文件保存成功: {file_path}")
        except Exception as e:
            if 'workbook' in locals():
                workbook.close()
            raise ValueError(f"保存文件失败: {str(e)}")
    
    def on_closing(self):
        # 窗口关闭时的处理
        print("窗口关闭")
        self.root.destroy()

if __name__ == "__main__":
    try:
        print("启动程序...")
        root = tk.Tk()
        # 设置窗口在最前面
        root.attributes('-topmost', True)
        root.update_idletasks()
        root.attributes('-topmost', False)
        
        app = AlternativePartAdder(root)
        
        # 添加异常捕获
        def handle_exception(exception, value, traceback):
            error_msg = f"程序发生错误: {str(value)}"
            print(error_msg)
            print(f"错误详情: {traceback.format_exc()}")
            messagebox.showerror("错误", error_msg)
        
        sys.excepthook = handle_exception
        
        print("进入主循环")
        root.mainloop()
    except Exception as e:
        import traceback
        error_msg = f"程序启动失败: {str(e)}"
        print(error_msg)
        print(f"错误详情: {traceback.format_exc()}")
        input("按Enter键退出...")